﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ModelLayer;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
/// <summary>
/// Dataaccess class for Persons
/// </summary>
namespace DBLayer
{
    public class PersonDB
    {
        //Variabler initialization
        private SqlConnection con;
        private SqlUtility sqlUtility; 
        public PersonDB()
        {
            sqlUtility = new SqlUtility();
        }
        //Insert Customer in the database 
        public int InsertCustomer(Customer _customer)
        {
            int result = -1;
            int personType = 1;
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query =
                "INSERT INTO Person (fName, lName, personAddress, zipCode, passportNumber, phoneNo, personType) VALUES (@fName, @lName, @personAddress, @zipCode, @passportNumber, @phoneNo, @personType)";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@fName", _customer.FName);
            cmd.Parameters.AddWithValue("@lName", _customer.LName);
            cmd.Parameters.AddWithValue("@personAddress", _customer.Address);
            cmd.Parameters.AddWithValue("@zipCode", _customer.ZipCode);
            cmd.Parameters.AddWithValue("@passportNumber", _customer.PassportNo);
            cmd.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);
            cmd.Parameters.AddWithValue("@personType", personType);
            
            if (_customer.PaymentMethod != null && _customer.AccountNo != null)
            {
                const string query2 =
                    "INSERT INTO Customer(paymentMethod, accountNo, phoneNo) VALUES (@paymentMethod, @accountNo, @phoneNo)";
                SqlCommand cmd2 = new SqlCommand(query2, connect);
                cmd2.Parameters.AddWithValue("@paymentMethod", _customer.PaymentMethod);
                cmd2.Parameters.AddWithValue("@accountNo", _customer.AccountNo);
                cmd2.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);
                result = sqlUtility.ExecuteMultipleData(cmd, cmd2, connect);
            }
            else
            {
                const string query2 =
                    "INSERT INTO Customer(phoneNo) VALUES (@phoneNo)";
                SqlCommand cmd2 = new SqlCommand(query2, connect);
                cmd2.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);
                result = sqlUtility.ExecuteMultipleData(cmd, cmd2, connect);
            }

            return result;
        }
        //Find Customer obj from the database and return it
        public Customer FindCustomerByPhoneNumber(int _phoneNo)
        {
            Customer retCustomer = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * FROM Person p, Customer c WHERE p.phoneNo=c.phoneNo AND p.phoneNo = @phoneNo";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@phoneNo", _phoneNo);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            retCustomer = BuildCustomer(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retCustomer;
        }
        //Update Customer in the database by a Customer obj
        public int UpdateCustomer(Customer _customer)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "UPDATE Person SET phoneNo = @phoneNo, fName = @fName, lName = @lName, personAddress = @personAddress, zipCode = @zipCode, passportNumber = @passportNumber WHERE phoneNo = @phoneNo";
            const string query2 = "UPDATE Customer SET phoneNo = @phoneNo, paymentMethod = @paymentMethod, accountNo = @accountNo WHERE phoneNo = @phoneNo";
          
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@fName", _customer.FName);
            cmd.Parameters.AddWithValue("@lName", _customer.LName);
            cmd.Parameters.AddWithValue("@personAddress", _customer.Address);
            cmd.Parameters.AddWithValue("@zipCode", _customer.ZipCode);
            cmd.Parameters.AddWithValue("@passportNumber", _customer.PassportNo);
            cmd.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);
            
            SqlCommand cmd2 = new SqlCommand(query2, connect);
            cmd2.Parameters.AddWithValue("@paymentMethod", _customer.PaymentMethod);
            cmd2.Parameters.AddWithValue("@accountNo", _customer.AccountNo);
            cmd2.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);

            return sqlUtility.ExecuteMultipleData(cmd, cmd2, connect);
        }
        //Build the Customer obj
        private Customer BuildCustomer(SqlDataReader _reader)
        {
                Customer temCustomer = new Customer();
                //Person
                temCustomer.FName = (string)_reader["fName"];
                temCustomer.LName = (string)_reader["lName"];
                temCustomer.Address = (string)_reader["personAddress"];
                temCustomer.ZipCode = (int)_reader["zipCode"];
                temCustomer.PassportNo = (string)_reader["passportNumber"];
                temCustomer.PhoneNo = (int)_reader["phoneNo"];
                //Customer
                temCustomer.PaymentMethod = (string)_reader["paymentMethod"];
                temCustomer.AccountNo = (string)_reader["accountNo"];

            return temCustomer;        
        }
        //return all Customer in the database
        public List<Customer> SelectAll()
        {
            List<Customer> customers = new List<Customer>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * FROM Person p, Customer c WHERE p.phoneNo=c.phoneNo";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Customer temCustomer = new Customer();
                            temCustomer = BuildCustomer(rdr);
                            customers.Add(temCustomer);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return customers;
        }
       //remove customer from database
        public int RemoveCustomer(Customer _customer)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from Person WHERE phoneNo = @phoneNo";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@phoneNo", _customer.PhoneNo);
            return sqlUtility.ExecuteData(cmd, connect);
        }
    }
}
